Oracle Backup and Recovery
Oracle Database Backup & Recovery -Simple Technique
User-Managed Offline Backup
The other names given to this kind of backup are
Closed/Cold/Consistent Database backup. No recovery is
needed in this kind of backup during the restore process.
Step 1:
Shut down the database
Connect to database as SYSDBA and then use the
SHUTDOWN statement with NORMAL, IMMEDIATE or
TRANSACTIONAL clause.
C:\> sqlplus /nolog
SQL>connect / as sysdba
SQL> SHUTDOWN NORMAL;
Remember that if there is any single user connected with the
database and you have used the NORMAL clause with the
SHUTDOWN statement then database will never shutdown
unless the user closes the session.
Step 2:
Backup all the data files
Once the database gets completely shutdown backup all the
data files associated with the database at the operating system
level or in other worlds using operating system commands e.g.
copy in Windows and cp in case of Unix.
Step 3:
Backup Parameter File, Redo Log Files and Archived Redo
Log Files
Backup all these files too using operating system command.
It's highly recommended and provides greater fault tolerance in
case of database failures.
Step 4:
Startup Oracle Database with MOUNT option
Start the oracle instance and mount the database but don't
open.
C:\>sqlplus /nolog
SQL>connect / as sysdba
SQL>startup mount;
Step 5:
Backup the Control File
Control file can be backed up using the following statement
and at the desired location.
ALTER DATABASE BACKUP CONTROLFILE TO 'c:\staging\ctl.bak';
The backup created in this fashion will be the binary copy of
the control file. We can also take the backup of control file,
which will be in text format in the following manner.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
As a result of this statement the text version of control file will
get copied to the location/folder specified in
USER_DUMP_DEST init.ora parameter.
Step 6:
Open the database
Now you have full database backup or in other words Whole
Database Backup. It's time to make the database available to
the users you executing the following statement.
ALTER DATABASE OPEN;
This completes our User-Managed Offline/Cold Database
Backup. Let's see now how we can perform User-Managed
Online/Hot Database Backup.
User-Managed Complete Recovery is done in two different
ways and it all depends on the mode in which you are running
the Oracle server. You can run Oracle server either in
ARCHIVELOG mode or NOARCHIVELOG mode. The way
we do the recovery in NOARCHIVELOG mode is different
from the way we do recovery in ARCHIVELOG mode. Let's
have a look on each procedure as follows.
User-Managed Complete Recovery in
NOARCHIVELOG Mode
In NOARCHIVELOG mode the recovery is very simple. All
you have to do is to restore the database files from your backup
to the original location and start the database.
Let's suppose one of your data file got corrupted. The time try
to start the Oracle Instance, it will give you an error. In this
case shutdown the database. Restore all the data files; redo log
files and control file back to their original location from your
last whole/complete cold backup.
Then start the Oracle database again by first running SQL and
then connecting as SYSDBA. This time you will not get any
error.
User-Managed Complete Recovery in
ARCHIVELOG Mode
Let's suppose one of your data file got corrupted/deleted. The
time you try to start the Oracle Instance, it will give you an
error with the name and location of the data files. If the Oracle
server is up and running and you don't want to shut it down
then follow the following steps.
Step 1:
Take the corrupted data file offline
Use the following SQL statement to take the corrupted/deleted
data file offline.
ALTER DATABASE DATAFILE 'c:\oracle\oradata\test\users01.dbf'
OFFLINE;
Step 2:
Restore the corrupted data file
Restore the corrupted data file by copy it from the backed up
location and pasting it to the original location at the operating
system level or in other words using operating systems
commands like 'copy' in windows or 'cp' in Unix/Linux.
Step 3:
Recover Data File
Recover the restored data file using the RECOVER
DATAFILE statement. Then apply all the available Redo Log
files by pressing "Enter" button on the keyboard whenever
asked.
C:\> sqlplus /nolog
SQL>connect / as sysdba
SQL>recover datafile 'c:\oracle\oradata\test\users01.dbf'
This will complete the recovery process and you will realize
that no data will get lost i.e. everything that you entered or
changed after the last whole database backup will get
recovered by Oracle by applying all the redo log files.
Difference between Recovery in ARCHIVELOG
mode & NOARCHIVELOG mode
In NOARCHIVELOG mode, the transactions that got
processed after the last complete backup, will get lost where as
in ARCHIVELOG mode no data will get lost i.e. everything
that you entered or changed after the last whole database
backup will get recovered by Oracle by applying all the redo
log files including the ones got archived by the Archiver
process.